import pandas as pd
import mysql.connector
from datetime import datetime
import logging

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

def update_lottery_data():
    try:
        # 读取Excel文件
        df = pd.read_excel('/Users/duchenyi/Downloads/双色球更新.xlsx')
        
        # 确保日期列是datetime类型
        df['日期'] = pd.to_datetime(df['日期'])
        
        # 连接到数据库
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='Duchenyi619',
            database='redAndBlue'
        )
        
        cursor = connection.cursor()
        
        # 准备插入语句
        insert_query = """
        INSERT INTO lottery_data 
        (draw_number, draw_date, red_ball_1, red_ball_2, red_ball_3, red_ball_4, red_ball_5, red_ball_6, blue_ball)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        
        # 遍历数据并插入
        for _, row in df.iterrows():
            values = (
                row['期号'],
                row['日期'],
                row['红球1'],
                row['红球2'],
                row['红球3'],
                row['红球4'],
                row['红球5'],
                row['红球6'],
                row['蓝球']
            )
            
            try:
                cursor.execute(insert_query, values)
                connection.commit()
                logging.info(f"成功插入数据: 期号 {row['期号']}, 日期 {row['日期'].strftime('%Y-%m-%d')}")
            except mysql.connector.Error as err:
                if err.errno == 1062:  # 重复数据错误
                    logging.warning(f"数据已存在，跳过: 期号 {row['期号']}, 日期 {row['日期'].strftime('%Y-%m-%d')}")
                else:
                    logging.error(f"插入数据时出错: {err}")
        
        cursor.close()
        connection.close()
        logging.info("数据更新完成")
        
    except Exception as e:
        logging.error(f"处理数据时出错: {e}")

if __name__ == "__main__":
    update_lottery_data() 